<?php
/**
 * Created by PhpStorm.
 * User: shaowei
 * Date: 2019/5/1
 * Time: 下午2:27
 */

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;


class DataDay extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'dataday:create';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '生成每日数据';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }


    // 激活
    public function getJihuo($date) {
        $ret = DB::select("select `channel_id`, count(*) f1 from ap_system_device where `created_at` >= '{$date}' and created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJihuo($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jihuo'=>$item->f1]);
        }

    }
    // 注册
    public function getZhuce($date) {
        $ret = DB::select("select `channel_id`, count(*) f1 from ap_user t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.created_at >= '{$date}'  and t1.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setZhuce($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['zhuce'=>$item->f1]);
        }

    }
    // 活跃(付费)
    public function getHuoyue($date) {
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_pay_order t2 on t1.user_id = t2.user_id where t1.day={$date} and t2.status = 1 group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id group by channel_id");
        return $ret;
    }
    public function setHuoyue($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['huoyue'=>$item->f1]);
        }

    }
    // 次留(付费)
    public function getLiucun($date) {
        $datenum = date("Ymd", strtotime($date)+3600*24);
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_pay_order t2 on t1.user_id = t2.user_id where t1.day={$datenum} and t2.status = 1 and t2.`created_at` >= '{$date}' and t2.created_at < date_add('{$date}', interval 1 day) group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id group by channel_id");
        return $ret;
    }
    public function setLiucun($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['liucun'=>$item->f1]);
        }

    }
    // 3日留存(付费)
    public function getLiucun3($date) {
        $datenum = date("Ymd", strtotime($date)+3600*24*2);
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_pay_order t2 on t1.user_id = t2.user_id where t1.day={$datenum} and t2.status = 1 and t2.`created_at` >= '{$date}' and t2.created_at < date_add('{$date}', interval 1 day) group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id group by channel_id");
        return $ret;
    }
    public function setLiucun3($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['liucun3'=>$item->f1]);
        }

    }
    // 7日留存(付费)
    public function getLiucun7($date) {
        $datenum = date("Ymd", strtotime($date)+3600*24*6);
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_pay_order t2 on t1.user_id = t2.user_id where t1.day={$datenum} and t2.status = 1 and t2.`created_at` >= '{$date}' and t2.created_at < date_add('{$date}', interval 1 day) group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id group by channel_id");
        return $ret;
    }
    public function setLiucun7($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['liucun7'=>$item->f1]);
        }

    }
    // 当日总付费
    public function getDrff($date) {
        $ret = DB::select("select `channel_id`, sum(price)/100 f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setDrff($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['drff'=>$item->f1]);
        }

    }
    // 当日总付费人数
    public function getDrffrs($date) {
        $ret = DB::select("select channel_id, count(*) f1 from (select `channel_id`, user_id  from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tt1 group by channel_id");
        return $ret;
    }
    public function setDrffrs($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['drffrs'=>$item->f1]);
        }

    }
    // 续费
    // 续费人数
    // 激活当日付费
    public function getJhdrff($date) {
        $ret = DB::select("select `channel_id`, sum(price)/100 f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrff($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrff'=>$item->f1]);
        }

    }
    // 激活当日付费人数
    public function getJhdrffrs($date) {
        $ret = DB::select(" select channel_id, count(*) f1 from (select `channel_id`, user_id from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdrffrs($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffrs'=>$item->f1]);
        }

    }
    // 激活当日付费笔数
    public function getJhdrffbs($date) {
        $ret = DB::select("select `channel_id`, count(*) f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrffbs($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffbs'=>$item->f1]);
        }

    }
    // 激活2日付费
    public function getJhdrff2($date) {
        $ret = DB::select("select `channel_id`, sum(price)/100 f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 2 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrff2($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrff2'=>$item->f1]);
        }

    }
    // 激活2日付费人数
    public function getJhdrffrs2($date) {
        $ret = DB::select(" select channel_id, count(*) f1 from (select `channel_id`, user_id from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 2 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdrffrs2($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffrs2'=>$item->f1]);
        }

    }
    // 激活2日付费笔数
    public function getJhdrffbs2($date) {
        $ret = DB::select("select `channel_id`, count(*) f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 2 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrffbs2($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffbs2'=>$item->f1]);
        }

    }
    // 激活30日付费
    public function getJhdrff30($date) {
        $ret = DB::select("select `channel_id`, sum(price)/100 f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 30 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrff30($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrff30'=>$item->f1]);
        }

    }
    // 激活30日付费人数
    public function getJhdrffrs30($date) {
        $ret = DB::select(" select channel_id, count(*) f1 from (select `channel_id`, user_id from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 30 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdrffrs30($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffrs30'=>$item->f1]);
        }

    }
    // 激活30日付费笔数
    public function getJhdrffbs30($date) {
        $ret = DB::select("select `channel_id`, count(*) f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 30 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrffbs30($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffbs30'=>$item->f1]);
        }

    }
    // 激活30日打折付费
    public function getJhdrffdz30($date) {
        $ret = DB::select("select `channel_id`, sum(price)/100 f1 from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.price in (4900,9900) and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 30 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`");
        return $ret;
    }
    public function setJhdrffdz30($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffdz30'=>$item->f1]);
        }

    }
    // 激活30日打折付费人数
    public function getJhdrffrsdz30($date) {
        $ret = DB::select(" select channel_id, count(*) f1 from (select `channel_id`, user_id from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.price in (4900,9900) and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 30 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdrffrsdz30($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffrsdz30'=>$item->f1]);
        }

    }


    // 激活当日打折付费人数
    public function getJhdrffrsdz($date) {
        $ret = DB::select(" select channel_id, count(*) f1 from (select `channel_id`, user_id from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.price in (4900,9900) and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdrffrsdz($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdrffrsdz'=>$item->f1]);
        }

    }

    // 激活当日2笔付费人数
    public function getJhdr2ffrs($date) {
        $ret = DB::select("select channel_id, count(*) f1 from (select `channel_id`, user_id, count(*) len from ap_pay_order t1 left join ap_system_device t2 on t1.`device_id` = t2.`device_id` where t1.status = 1 and t1.created_at >= '{$date}' and t1.created_at <  date_add('{$date}', interval 1 day) and  t2.created_at >= '{$date}' and t2.created_at <  date_add('{$date}', interval 1 day) group by `channel_id`, user_id having len > 1) tx1 group by tx1.channel_id");
        return $ret;
    }
    public function setJhdr2ffrs($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhdr2ffrs'=>$item->f1]);
        }
    }


    // 激活付费次留
    public function getJHFFLiucun($date) {
        $datenum = date("Ymd", strtotime($date)+3600*24);
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_pay_order t2 on t1.user_id = t2.user_id where t1.day={$datenum} and t2.status = 1 and t2.`created_at` >= '{$date}' and t2.created_at < date_add('{$date}', interval 1 day) group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id and tt2.`created_at` >= '{$date}' and tt2.created_at < date_add('{$date}', interval 1 day)   group by channel_id");
        return $ret;
    }
    public function setJHFFLiucun($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhffliucun'=>$item->f1]);
        }
    }

    // 激活次留
    public function getJHLiucun($date) {
        $datenum = date("Ymd", strtotime($date)+3600*24);
        $ret = DB::select("select channel_id,count(*) f1 from (select t2.`device_id` from ap_user_active t1 left join ap_user t2 on t1.user_id = t2.id where t1.day={$datenum} and t2.`created_at` >= '{$date}' and t2.created_at < date_add('{$date}', interval 1 day) group by t2.`device_id`) tt1 left join ap_system_device tt2 on tt1.device_id = tt2.device_id group by channel_id");
        return $ret;
    }
    public function setJHLiucun($date, $data) {
        foreach ($data as $item) {
            DB::table('stat_day')->updateOrInsert(['date'=>$date,'channel_id'=>$item->channel_id], ['jhliucun'=>$item->f1]);
        }
    }



    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {

        for ($i = 1; $i < 31; $i++) {
            $time = strtotime("-{$i} day");
            if ($time < 1664553600) {
                break;
            }

            $date = date("Y-m-d",strtotime("-{$i} day"));
            $datenum = date("Ymd",strtotime("-{$i} day"));
            $data = $this->getJihuo($date);
            $this->setJihuo($date, $data);

            $data = $this->getZhuce($date);
            $this->setZhuce($date, $data);

            $data = $this->getHuoyue($datenum);
            $this->setHuoyue($date, $data);

            $data = $this->getLiucun($date);
            $this->setLiucun($date, $data);

            $data = $this->getLiucun3($date);
            $this->setLiucun3($date, $data);

            $data = $this->getLiucun7($date);
            $this->setLiucun7($date, $data);

            $data = $this->getDrff($date);
            $this->setDrff($date, $data);

            $data = $this->getDrffrs($date);
            $this->setDrffrs($date, $data);

            $data = $this->getJhdrff($date);
            $this->setJhdrff($date, $data);

            $data = $this->getJhdrffrs($date);
            $this->setJhdrffrs($date, $data);

            $data = $this->getJhdrffbs($date);
            $this->setJhdrffbs($date, $data);


            $data = $this->getJhdrff2($date);
            $this->setJhdrff2($date, $data);

            $data = $this->getJhdrffrs2($date);
            $this->setJhdrffrs2($date, $data);

            $data = $this->getJhdrffbs2($date);
            $this->setJhdrffbs2($date, $data);


            $data = $this->getJhdrff30($date);
            $this->setJhdrff30($date, $data);

            $data = $this->getJhdrffrs30($date);
            $this->setJhdrffrs30($date, $data);

            $data = $this->getJhdrffbs30($date);
            $this->setJhdrffbs30($date, $data);

            $data = $this->getJhdrffdz30($date);
            $this->setJhdrffdz30($date, $data);

            $data = $this->getJhdrffrsdz30($date);
            $this->setJhdrffrsdz30($date, $data);


            $data = $this->getJhdrffrsdz($date);
            $this->setJhdrffrsdz($date, $data);


            $data = $this->getJhdr2ffrs($date);
            $this->setJhdr2ffrs($date, $data);

            $data = $this->getJHFFLiucun($date);
            $this->setJHFFLiucun($date, $data);

            $data = $this->getJHLiucun($date);
            $this->setJHLiucun($date, $data);
        }

    }
}
